using System;
using System.Text;
 using System.Data;
 using System.Data.SqlClient;
 using System.Collections;

namespace HREDU.Hoang_Tuan.Classes
{
    /// <summary>
    /// Summary description for CDONVI.
    /// </summary>
    public class CDONVI
    {
        private int _id_donvi;
        private string _nhom;
        private bool _cuoi;
        private string _ten_donvi;
        private string _dienthoai;
        private string _fax;
        private string _diachi;

        public CDONVI()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public int ID_DONVI
        {
            get { return _id_donvi; }
            set { _id_donvi = value; }
        }

        public string NHOM
        {
            get { return _nhom; }
            set { _nhom = value; }
        }

        public bool CUOI
        {
            get
            {
                return _cuoi;
            }
            set
            {
                _cuoi = value;
            }
        }

        public string TEN_DONVI
        {
            get { return _ten_donvi; }
            set { _ten_donvi = value; }
        }

        public string DIENTHOAI
        {
            get { return _dienthoai; }
            set { _dienthoai = value; }
        }

        public string FAX
        {
            get { return _fax; }
            set { _fax = value; }
        }

        public string DIACHI
        {
            get { return _diachi; }
            set { _diachi = value; }
        }
        # region "Them, xoa, sua"

        public int ColumnLength(string column)
        {
            return (int)MITI.BaseDB.Instance.ExecSql_DataValue("select COL_LENGTH('DONVI','" + column + "')") / 2;
        }

        public bool IsExists()
        {
            string strSQL = "select * from DONVI where ten_donvi =N'" + _ten_donvi + "'";
            DataSet ds = MITI.BaseDB.Instance.ExecSql_DataSet(strSQL);
            if ((ds.Tables[0].Rows.Count > 0))
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public bool IsUsed()
        {
            string strSQL = "select dbo.fc_IsDonViUsed('" + _nhom.ToString() + "')";
            object kq = MITI.BaseDB.Instance.ExecSql_DataValue(strSQL);
            return (bool)kq;
        }

       
        # endregion
    }

    public class CDONVICollection
    {
        public static string tableName = "DONVI";
        public static string idColumnName = "id_donvi";
        public static string nhomColumnName = "nhom";
        public static string cuoiColumnName = "cuoi";
        public static string tenColumnName = "ten_donvi";
        public static int lenNhom = 3;

        public static int ColumnLength(string column)
        {
            int len = int.Parse(MITI.BaseDB.Instance.ExecSql_DataValue("select COL_LENGTH('DONVI','" + column + "')").ToString());
            return len / 2;
        }

        public static bool IsExists(string nhomCha, string ten_donvi, int cuoi)
        {
            string strSQL;
            strSQL = "select count(*) from DONVI where left(nhom," + nhomCha.Length + ") = '" + nhomCha + "' and len(nhom) = " + (nhomCha.Length + lenNhom) + " and ten_donvi like N'" + ten_donvi + "' and cuoi = " + cuoi;
            int value = (int)MITI.BaseDB.Instance.ExecSql_DataValue(strSQL);
            if (value > 0)
            {
                return true;
            }
            return false;
        }

        public static bool IsExistsInNHANVIEN(int id_donvi)
        {
            string strSQL;
            strSQL = "select count(*) from NHANVIEN where "; 
            strSQL += " id_donvi =" + id_donvi.ToString();
            strSQL += " or id_chucvu_dang = " + id_donvi.ToString();
            strSQL += " or id_chucvu_cq = " + id_donvi.ToString();
            strSQL += " or id_chucvu_dt = " + id_donvi.ToString();
            int value = (int)MITI.BaseDB.Instance.ExecSql_DataValue(strSQL);
            if (value > 0)
            {
                return true;
            }
            return false;
        }

        public static bool IsExistsChildNodes(string nhomCha, bool cuoi)
        {
            string strSQL = "";
            strSQL = "select count(*) from DONVI where ";
            strSQL += " left(nhom," + nhomCha.Length.ToString() + ") = '" + nhomCha + "'";
            strSQL += " and len(nhom) > " + nhomCha.Length.ToString() + (cuoi == true ? " and cuoi = 1" : "").ToString();
            int value = (int)MITI.BaseDB.Instance.ExecSql_DataValue(strSQL);
            if (value > 0)
            {
                return true;
            }
            return false;
        }

        public static void CountChildNodes(string nhomCha, ref int folder, ref int file)
        {
            string strSQL;
            strSQL = "select count(*) from DONVI";
            strSQL += " where left(nhom," + nhomCha.Length + ") = '" + nhomCha + "'";
            strSQL += " and len(nhom) = " + (nhomCha.Length + lenNhom).ToString();
            strSQL += " and cuoi = 0";

            strSQL += "select count(*) from DONVI";
            strSQL += " where left(nhom," + nhomCha.Length + ") = '" + nhomCha + "'";
            strSQL += " and len(nhom) = " + (nhomCha.Length + lenNhom).ToString();
            strSQL += " and cuoi = 1";
            DataSet ds = MITI.BaseDB.Instance.ExecSql_DataSet(strSQL);
            folder = (int)ds.Tables[0].Rows[0][0];
            file = (int)ds.Tables[1].Rows[0][0];
        }

        public static long Update(DataRow dr)
        {
            string strSQL;
            strSQL = "update DONVI" + " set ten_donvi = " + dr["ten_donvi"].ToString() + " cuoi = " + dr["cuoi"].ToString() + " where nhom = " + dr["nhom"].ToString();
            return MITI.BaseDB.Instance.ExecSql_NoneQuery(strSQL);
        }

        public static bool Insert(CDONVI objDonVi)
        {
            StringBuilder objStringBuilder = new StringBuilder(250);
            {
                objStringBuilder.Append("INSERT INTO DONVI(nhom, cuoi, ten_donvi,dienthoai,fax,diachi) VALUES(");
                objStringBuilder.Append(MITI.BaseDB.Instance.GetParamPlaceHolder("nhom", true));
                objStringBuilder.Append(MITI.BaseDB.Instance.GetParamPlaceHolder("cuoi", true));
                objStringBuilder.Append(MITI.BaseDB.Instance.GetParamPlaceHolder("ten_donvi", true));
                objStringBuilder.Append(MITI.BaseDB.Instance.GetParamPlaceHolder("dienthoai", true));
                objStringBuilder.Append(MITI.BaseDB.Instance.GetParamPlaceHolder("fax", true));
                objStringBuilder.Append(MITI.BaseDB.Instance.GetParamPlaceHolder("diachi", false));
                objStringBuilder.Append(")");
            }

            string sInsert = objStringBuilder.ToString();
            SqlCommand cmd = MITI.BaseDB.Instance.CreateCommand(sInsert, CommandType.Text);
            long lRowsAffected = 0;
            try
            {
                {
                    cmd.Transaction = cmd.Connection.BeginTransaction();
                    AddParameter(ref cmd,"nhom", objDonVi.NHOM);
                    AddParameter(ref cmd,"cuoi", objDonVi.CUOI);
                    AddParameter(ref cmd, "ten_donvi", objDonVi.TEN_DONVI);
                    AddParameter(ref cmd, "dienthoai", objDonVi.DIENTHOAI);
                    AddParameter(ref cmd, "fax", objDonVi.FAX);
                    AddParameter(ref cmd, "diachi", objDonVi.DIACHI);
                    lRowsAffected = cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();
            }
            catch (System.Exception ex)
            {
                cmd.Transaction.Rollback();
                throw ex;
            }
            finally
            {
                MITI.BaseDB.Instance.CloseConnect();
            }
            return true;
        }

        public static long Update(CDONVI objDonVi)
        {
            long lRowsAffected = 0;
            string strSQL = "UPDATE DONVI SET";
            strSQL += " nhom = " + MITI.BaseDB.Instance.GetParamPlaceHolder("nhom", true);
            strSQL += " cuoi = " + MITI.BaseDB.Instance.GetParamPlaceHolder("cuoi", true);
            strSQL += " ten_donvi = " + MITI.BaseDB.Instance.GetParamPlaceHolder("ten_donvi", true);
            strSQL += " dienthoai = " + MITI.BaseDB.Instance.GetParamPlaceHolder("dienthoai", true);
            strSQL += " fax = " + MITI.BaseDB.Instance.GetParamPlaceHolder("fax", true);
            strSQL += " diachi = " + MITI.BaseDB.Instance.GetParamPlaceHolder("diachi", false);
            strSQL += " WHERE id_donvi = " + MITI.BaseDB.Instance.GetParamPlaceHolder("id_donvi", false);
            
            SqlCommand cmd = MITI.BaseDB.Instance.CreateCommand(strSQL, CommandType.Text);
            try
            {
                    cmd.Transaction = cmd.Connection.BeginTransaction();
                    AddParameter(ref cmd,"nhom", objDonVi.NHOM);
                    AddParameter(ref cmd,"cuoi", objDonVi.CUOI);
                    AddParameter(ref cmd,"ten_donvi", objDonVi.TEN_DONVI);
                    AddParameter(ref cmd,"dienthoai", objDonVi.DIENTHOAI);
                    AddParameter(ref cmd,"fax", objDonVi.FAX);
                    AddParameter(ref cmd,"diachi", objDonVi.DIACHI);
                    AddParameter(ref cmd,"id_donvi", objDonVi.ID_DONVI);
                    lRowsAffected = cmd.ExecuteNonQuery();
                    cmd.Transaction.Commit();
            }
            catch (System.Exception ex)
            {
                cmd.Transaction.Rollback();
                throw ex;
            }
            finally
            {
                MITI.BaseDB.Instance.CloseConnect();
            }
            return lRowsAffected;
        }

        public static long DeleteByNhom(string nhom, bool bDeleteAll)
        {
            if (bDeleteAll)
            {
                int lRowsAffected;
                string strSQL;
                strSQL = "delete from DONVI where left(nhom," + nhom.Length.ToString() + ") = '" + nhom + "' and len(nhom) >= " + nhom.Length.ToString();
                lRowsAffected = (int)MITI.BaseDB.Instance.ExecSql_NoneQuery(strSQL);
                return lRowsAffected;
            }
            else
            {
                long lRowsAffected = 0;
                string strSQL = "delete from DONVI where";
                strSQL += " nhom = " + MITI.BaseDB.Instance.GetParamPlaceHolder("nhom", false);
                SqlCommand cmd = MITI.BaseDB.Instance.CreateCommand(strSQL, CommandType.Text); 
                try
                {
                    cmd.Transaction = cmd.Connection.BeginTransaction();
                    AddParameter(ref cmd, "nhom", nhom);
                    lRowsAffected = cmd.ExecuteNonQuery();
                    cmd.Transaction.Commit();
                }
                catch (System.Exception ex)
                {
                    cmd.Transaction.Rollback();
                    throw ex;
                }
                finally
                {
                    MITI.BaseDB.Instance.CloseConnect();
                }
                return lRowsAffected;
            }
        }

        public static string TaoNhom(string nhomCha)
        {
            string nhom;
            SqlCommand cmd = null;
            try
            {
                SqlParameter[] par = new SqlParameter[2];
                par[0] = new SqlParameter("@nhomCha", SqlDbType.NVarChar, 30);
                par[0].Value = nhomCha;
                par[1] = new SqlParameter("@nhom", SqlDbType.NVarChar, 30);
                par[1].Direction = ParameterDirection.Output;
                cmd = MITI.BaseDB.Instance.ExecSqlProcedure_Command("sp_HTTaoNhomDonVi", par);
                nhom = cmd.Parameters["@nhom"].Value.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return nhom;
        }

        public static bool KiemTraLengthChuyenDoiNhom(string maDrag, string maDrop)
        {
           string strSQL = "select dbo.fc_HTKiemTraLengthChuyenDoiNhom('" + maDrag + "','" + maDrop +"')";
           return (bool)MITI.BaseDB.Instance.ExecSql_DataValue(strSQL);
        }

        public static string ChuyenDoiNhom(string maDrag, string maDrop)
        {
            SqlCommand cmd = null;
            try
            {
                SqlParameter[] par = new SqlParameter[2];
                par[0] = new SqlParameter("@maDrag", SqlDbType.NVarChar, 30);
                par[0].Value = maDrag;
                par[0].Direction = ParameterDirection.InputOutput;
                par[1] = new SqlParameter("@maDrop", SqlDbType.NVarChar, 30);
                par[1].Value = maDrop;
                cmd = MITI.BaseDB.Instance.ExecSqlProcedure_Command("sp_HTChuyenDoiNhom", par);
                maDrag = cmd.Parameters["@maDrag"].Value.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return maDrag;
        }

        public static CDONVI LayCoQuanTheoNhom(string nhom)
        {
            string strSQL;
            strSQL = "SELECT * FROM DONVI" + " WHERE nhom = '" + nhom + "'";
            CDONVI objDonVi = GetDonViTheoSQL(strSQL);
            return objDonVi;
        }

        public static CDONVI LayCoQuanTheoID(int id_coquan)
        {
            string strSQL;
            strSQL = "SELECT * FROM DONVI" + " WHERE id_donvi = '" + id_coquan + "'";
            CDONVI objDonVi = GetDonViTheoSQL(strSQL);
            return objDonVi;
        }

        public static CDONVI LayCoQuanTheoTen(string ten_donvi)
        {
            string strSQL;
            strSQL = "SELECT * FROM DONVI" + " WHERE ten_donvi like N'" + ten_donvi + "'";
            CDONVI objDonVi = GetDonViTheoSQL(strSQL);
            return objDonVi;
        }


        public static DataTable LayCoQuanTheoNhom_DataTable(string nhom)
        {
            string strSQL;
            strSQL = "SELECT * FROM DONVI" + " WHERE nhom = '" + nhom + "'";
            DataSet ds = null;
            try
            {
                ds = MITI.BaseDB.Instance.ExecSql_DataSet(strSQL);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return ds.Tables[0];
        }


        //Neu khong quan ly theo thuoc tinh CUOI, tuc khong loc theo thuoc tinh CUOI
        public static DataTable LayCoQuanTheoNhomCha_DataTable(string nhomCha)
        {
            string strSQL;
            strSQL = "SELECT * from DONVI" + " where left(nhom, " + nhomCha.Length.ToString() + ") = '" + nhomCha + "'" + " and len(nhom) = " + (nhomCha.Length + lenNhom).ToString() + " order by nhom";
            DataSet ds = null;
            try
            {
                ds = MITI.BaseDB.Instance.ExecSql_DataSet(strSQL);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return ds.Tables[0];
        }

        //Neu co quan ly theo thuoc tinh CUOI, tuc co loc theo thuoc tinh cuoi
        public static DataTable LayCoQuanTheoNhomCha_DataTable(string nhomCha, int cuoi)
        {
            string strSQL;
            strSQL = "SELECT * from DONVI" + " where left(nhom, " + nhomCha.Length.ToString() + ") = '" + nhomCha + "'" + " and len(nhom) = " + (nhomCha.Length + lenNhom).ToString() + " and cuoi = " + cuoi.ToString() + " and ten_donvi is not null order by nhom";
            //strSQL = "SELECT * from DONVI" + " where left(nhom, " + nhomCha.Length.ToString() + ") = '" + nhomCha + "'" + " and len(nhom) = " + (nhomCha.Length + lenNhom).ToString() + " and cuoi = " + cuoi.ToString() + " order by nhom";
            DataSet ds = null;
            try
            {
                ds = MITI.BaseDB.Instance.ExecSql_DataSet(strSQL);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return ds.Tables[0];
        }

        public static DataTable TimTheoTen_DataTable(string tenCanTim, string nhom)
        {
            string strSQL;
            strSQL = "SELECT * FROM DONVI" + " WHERE ten_donvi like N'" + tenCanTim + "%'";

            if (nhom != "")
            {
                strSQL += " and left(nhom," + nhom.Length.ToString() + ") = '" + nhom + "'" + " and len(nhom) > " + nhom.Length.ToString();
            }

            return MITI.BaseDB.Instance.ExecSql_DataSet(strSQL).Tables[0];
        }

        public static bool LayGiaTriCuoiTheoNhom(string nhom)
        {
            string strSQL = "";
            strSQL = "select isnull(cuoi,0) from DONVI where nhom ='" + nhom + "'";
            bool kq;
            try
            {
                kq = (bool)MITI.BaseDB.Instance.ExecSql_DataValue(strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return kq;
        }

        /// <summary>
        /// Adds a new parameter to the specified command.
        /// </summary>
        /// <param name="cmd">The <see cref="System.Data.SqlClient.SqlCommand"/> object to add the parameter to.</param>
        /// <param name="paramName">The name of the parameter.</param>
        /// <param name="value">The value of the parameter.</param>
        /// <returns>A reference to the added parameter.</returns>
        protected static SqlParameter AddParameter(ref SqlCommand cmd, string paramName, object value)
        {
            SqlParameter parameter = null ;
            switch (paramName)
            {
                case "id_donvi":
                    parameter = MITI.BaseDB.Instance.CreateParameter(ref cmd, paramName, DbType.Int32, (value == null ? DBNull.Value : value));
                    break;
                case "nhom":
                case "ten_donvi":
                case "dienthoai":
                case "fax":
                case "diachi":
                    parameter = MITI.BaseDB.Instance.CreateParameter(ref cmd, paramName, DbType.String, (value == null ? DBNull.Value : value));
                    break;
                case "cuoi":
                    parameter = MITI.BaseDB.Instance.CreateParameter(ref cmd, paramName, DbType.Boolean, (value == null ? DBNull.Value : value));
                    break;
            }
            return parameter;
        }

        public static CDONVI GetDonViTheoSQL(string strSQL)
        {
            SqlDataReader objReader = null;
            CDONVI objDonVi=null;
            try
            {
                objReader = MITI.BaseDB.Instance.ExecSql_DataReader(strSQL);
                while (objReader.Read())
                {
                     objDonVi= GetObjectByDataReader(objReader);
                }
                if ((objReader != null))
                {
                    objReader.Close();
                }
                MITI.BaseDB.Instance.CloseConnect();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return objDonVi;
        }

        public static CDONVI GetObjectByDataReader(SqlDataReader objReader)
        {
            CDONVI objDonVi = new CDONVI();
            objDonVi.ID_DONVI = objReader.GetInt32(0);
            objDonVi.NHOM = objReader.GetString(1);
            objDonVi.CUOI = objReader.GetBoolean(2);
            objDonVi.TEN_DONVI = objReader.GetString(3);
            objDonVi.DIENTHOAI = (objReader.GetValue(4) == DBNull.Value ? null : objReader.GetString(4));
            objDonVi.FAX = (objReader.GetValue(5) == DBNull.Value ? null : objReader.GetString(5));
            objDonVi.DIACHI = (objReader.GetValue(6) == DBNull.Value ? null : objReader.GetString(6));
            return objDonVi;
        }
    }
}	
